package cn.hp.crm.dao.impl;

import cn.hp.crm.dao.UserDao;
import cn.hp.crm.model.User;
import cn.hp.crm.util.DBUtil;
import cn.hp.crm.util.PageBean;
import cn.hutool.core.util.StrUtil;
import com.mysql.cj.protocol.Resultset;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class UserDaoImpl implements UserDao {
    @Override
    public boolean selectOneByNameAndPassword(String userName, String password) {
        String sql = "select id from c_user where user_name = ? and password = ? and is_del = 1 ";
        Object [] objects = {userName , password};
        ResultSet select = DBUtil.select(sql, objects);
        boolean flag = false;
        try {
            flag = select.next();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return flag;
    }

    @Override
    public List<User> selectAll() {
        String sql = "select * from c_user ";
        Object [] objs = {} ;
        ResultSet resultSet = DBUtil.select(sql, objs);

        List<User> users = new ArrayList<>();

        try {
           while(resultSet.next()) {
             User user = new User(resultSet.getInt(1),
                     resultSet.getString(2),
                     resultSet.getString(3),
                     resultSet.getString(4),
                     resultSet.getString(5),
                     resultSet.getInt(6),
                     resultSet.getInt(7),
                     resultSet.getDate(8),
                     resultSet.getDate(9));
             users.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close();
        }
        return users;
    }

//    获取条件下的全部数据
    @Override
    public int getAllCount(User user) {
        String sql = "select count(id) from c_user where 1 = 1 ";
//        hutool的字符串工具类 当不为空时，将条件加上
        if (!StrUtil.isEmpty(user.getUserName())){
            sql = sql + " and user_name like '%" + user.getUserName() + "%'";
        }
        if (!StrUtil.isEmpty(user.getRealName())){
            sql = sql + " and real_name like '%" + user.getRealName() + "%'";
        }
        if (user.getType() != 0){
            sql = sql + " and type = " + user.getType() ;
        }

        Object [] objects = {};
        ResultSet select = DBUtil.select(sql, objects);
        int count = 0 ;
        try {
            while (select.next()){
               count = select.getInt(1);
            }
        }catch (SQLException e){
            e.printStackTrace();
        }
        return count ;
    }

    @Override
    public List<User> getUsersLimit(PageBean pageBean, User user) {
        List<User> users = new ArrayList<>();
        String sql = "select * from c_user where 1 =1 ";
        if (!StrUtil.isEmpty(user.getUserName())){
            sql = sql + "and user_name like '%" + user.getUserName() + "%'";
        }
        if (!StrUtil.isEmpty(user.getRealName())){
            sql = sql + "and real_name like '%" + user.getRealName() + "%'";
        }
        if (user.getType() != 0){
            sql = sql + "and type = " + user.getType() ;
        }
        sql = sql + " limit " + (pageBean.getCurrentPage() - 1)*pageBean.getDataVolumePerPage() +" ," + pageBean.getDataVolumePerPage();

        Object [] objs = {} ;
        ResultSet resultSet = DBUtil.select(sql, objs);
        try {
            while(resultSet.next()) {
                User user1 = new User(resultSet.getInt(1),
                        resultSet.getString(2),
                        resultSet.getString(3),
                        resultSet.getString(4),
                        resultSet.getString(5),
                        resultSet.getInt(6),
                        resultSet.getInt(7),
                        resultSet.getDate(8),
                        resultSet.getDate(9));
                users.add(user1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close();
        }
        return users;
    }

    @Override
    public int updatePasswordById(Integer id) {
        String sql = "update c_user set password = 123456 where id = ?";
        Object objects [] = {id};
        int update = DBUtil.update(sql, objects);
        return update;
    }

    @Override
    public int deleteById(String []ids) {
        String sql = "delete from c_user where id in ( ? " ;
        for (int i = 1  ; i < ids.length ; i ++){
            sql += ", ? ";
        }
          sql += ")" ;
        int update = DBUtil.update(sql, ids);

        return update;
    }

//    通过用户名查询是否存在数据
    @Override
    public boolean selectOneByName(String userName) {
        String sql = "select user_name from c_user where user_name = ?";
        Object [] objects = {userName};
        ResultSet select = DBUtil.select(sql, objects);
        boolean flag = true ;
        try {
            flag = select.next();
        }catch (SQLException e){
            e.printStackTrace();
        }
        return flag;
    }

//    新增数据
    @Override
    public int add(User parameter) {
        String sql = "insert into c_user(user_name ,password , real_name , type ,is_del , create_time ,modify_time)  values(?,?,?,?,1,now(),now())";
        Object [] objects = {parameter.getUserName(), parameter.getPassword() , parameter.getRealName() , parameter.getType()};
        int update = DBUtil.update(sql, objects);
        return update;
    }

    @Override
    public int updatePasswordByName(String userName, String newPassword) {
//        通过名称修改密码
        String sql = "update c_user set password = ? where user_name = ?";
        Object [] objects = {newPassword , userName};
        int update = DBUtil.update(sql, objects);
        return update;
    }

//    通过名称修改头像值
    @Override
    public int UpdateImgByName(String user, String s) {
        String sql = "update c_user set img = ? where user_name = ?";
        Object [] objects = {s , user};
        int update = DBUtil.update(sql, objects);
        return update;
    }

//    通过名称查询头像值
    @Override
    public String selectImgByName(String user) {
        String sql = "select img from c_user where user_name = ?";
        Object [] objects = {user};
        ResultSet select = DBUtil.select(sql, objects);
        String img = "" ;
        try {
            if (select.next()){
                img = select.getString(1);
            }
        }catch (SQLException e){
            e.printStackTrace();
        }
        return img ;
    }

//    通过type查询人数
    @Override
    public List selectCountByType() {
        String sql = "select type ,count(*) from c_user group by type";
        Object o[] = {};
        ResultSet select = DBUtil.select(sql, o);
//        操作select 将数据进行封装
        List list = new ArrayList( );
        try {
            while (select.next()){
                int type = select.getInt(1);
                int count = select.getInt(2);
                Map map = new HashMap();
                map.put("type",type);
                map.put("count",count);
                list.add(map);
            }
        }catch (SQLException e){
            e.printStackTrace();
        }

        return list;
    }


}
